--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RISK_BAD_LOAN_DMIG.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0       2018-05-28  MAYANTING          1.CREATE THE PROCEDURE
--

     INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RISK_BAD_LOAN_DMIG PARTITION(DATA_DATE= '#V_DATA_DATE#')
     SELECT SUBSTR('#V_DATA_DATE#',1,6)                      AS DATA_MONTH
           ,ORG.ORG_CODE_1                                   AS ORG_CODE_1
           ,ORG.ORG_DSCR_1                                   AS ORG_DSCR_1
           ,ORG.ORG_CODE_2                                   AS ORG_CODE_2
           ,ORG.ORG_DSCR_2                                   AS ORG_DSCR_2
           ,ORG.ORG_CODE_3                                   AS ORG_CODE_3
           ,ORG.ORG_DSCR_3                                   AS ORG_DSCR_3
           ,AREA.AREA_CODE_1                                 AS AREA_CODE_1
           ,AREA.AREA_DSCR_1                                 AS AREA_DSCR_1
           ,AREA.AREA_CODE_3                                 AS AREA_CODE_2
           ,AREA.AREA_DSCR_3                                 AS AREA_DSCR_2
           ,AREA.AREA_CODE_4                                 AS AREA_CODE_3
           ,AREA.AREA_DSCR_4                                 AS AREA_DSCR_3
           ,T.CUSTOMER_TYPE                                  AS CUSTOMER_TYPE
           ,T.PRODUCT_CATEGORY                               AS PRODUCT_CATEGORY
           ,T.LPIC                                           AS LPIC
           ,T.LOANS_ACTUALLY                                 AS LOANS_ACTUALLY
           ,T.ENTERPRISE_SCALE                               AS ENTERPRISE_SCALE
           ,T.LOAN_PERIOD                                    AS LOAN_PERIOD
           ,T.ENTERPRISE_INVESTOR                            AS ENTERPRISE_INVESTOR
           ,T.GUARANTY_STYLE                                 AS GUARANTY_STYLE
           ,T.LOAN_QUALITY                                   AS LOAN_QUALITY
           ,T.LOAN_STATUS                                    AS LOAN_STATUS
           ,T.CCY                                            AS CCY
           ,SUM(LOAN_BAL   )/100000000                       AS LOAN_BAL               -- 当月贷款余额
           ,SUM(LOAN_BAL_LM)/100000000                       AS LOAN_BAL_LM            -- 上月贷款余额
           ,SUM(LOAN_BAL_LY)/100000000                       AS LOAN_BAL_LY            -- 去年同期贷款余额
           ,SUM(WSUM_BAL   )/100000000                       AS WSUM_BAL               -- 当月余额加权金额
           ,SUM(WSUM_BAL_LM)/100000000                       AS WSUM_BAL_LM            -- 上月余额加权金额
           ,SUM(WSUM_BAL_LY)/100000000                       AS WSUM_BAL_LM            -- 去年同期余额加权金额
    FROM
    (
        -- 上月向下迁徙,余额取本月
        SELECT   YE.FIN_ORG_NO                      AS FIN_ORG_NO           -- 金融机构编码
                ,YE.CLIENT_TYPE                     AS CUSTOMER_TYPE        --  客户类型 单位客户=0；个人客户=1
                ,PRO.LOAN_PRODUCT_ID                AS PRODUCT_CATEGORY     --  贷款产品类别  展示一级
                ,YE.CLIENT_INDUSTRY                 AS LPIC                 --  企业分行业(贷款主体行业类别)
                ,ACTU.INDUSTRY_CODE_1               AS LOANS_ACTUALLY       --  行业投向  展示一级
                ,YE.ENTERPRISE_SCALE                AS ENTERPRISE_SCALE     --  企业规模 大、中、小、微型企业分别对应代码：CS01、CS02、CS03、CS04 非企业类单位对应代码：CS05。
                ,YE.TERM_CODE                       AS LOAN_PERIOD          --  贷款期限 1：6个月（含）以内 2：6个月至1年（含） 3：1至3年（含） 4：3至5年（含）5：5至10年（含）6：10年以上 7：未定期限"
                ,ECO.ECO_SEC_CAT_ID_2               AS ENTERPRISE_INVESTOR  -- 企业出资人经济成分 展示二级
                ,GUAR.LOAN_GUAR_TYPE_ID_1           AS GUARANTY_STYLE       -- 担保方式   展示一级
                ,YE.FIVE_CLASS                      AS LOAN_QUALITY         -- 贷款质量  FQ01-正常类贷款 FQ02-关注类贷款 FQ03-次级类贷款 FQ04-可疑类贷款 FQ05-损失类贷款
                ,YE.LOAN_STATUS                     AS LOAN_STATUS          -- 贷款状态 FS01-正常 FS02-展期 FS03-逾期 FS09-缩期 ...
                ,YE.CCY                             AS CCY                  -- 币种
                ,COALESCE(YE.ACTUAL_BAL,0)          AS LOAN_BAL             -- 当月贷款余额
                ,0                                  AS LOAN_BAL_LM          -- 上月贷款余额
                ,0                                  AS LOAN_BAL_LY          -- 去年同期贷款余额
                ,COALESCE(YE.WSUM_BAL,0)            AS WSUM_BAL             -- 当月贷款余额加权值
                ,0                                  AS WSUM_BAL_LM          -- 上月余额加权金额
                ,0                                  AS WSUM_BAL_LY          -- 去年同期余额加权金额
        FROM EDW.AG_LOAB_INFO YE
        -- 关联上月贷款余额,取状态为正常类贷款,FQ02-关注类贷款
        LEFT JOIN (SELECT DUEBILL_NO FROM  EDW.AG_LOAB_INFO WHERE FIVE_CLASS IN ('FQ01','FQ02') AND DATA_DATE='#V_PREV_MONTH_DATE#') A ON A.DUEBILL_NO=YE.DUEBILL_NO
        -- 贷款产品类别
        LEFT JOIN DIMENSION.DIM_LOAN_PRODUCT PRO  ON YE.BUSINESS_TYPE=PRO.LOAN_PRODUCT_ID AND PRO.IS_VALID= '1' AND '#V_DATA_DATE#' BETWEEN PRO.START_DATE AND PRO.END_DATE
        -- 实际投向
        LEFT JOIN DIMENSION.DIM_INDUSTRY ACTU  ON YE.LOAN_INDUSTRY=ACTU.INDUSTRY_CODE_3 AND ACTU.IS_VALID= '1' AND '#V_DATA_DATE#' BETWEEN ACTU.START_DATE AND ACTU.END_DATE
        -- 企业出资人经济成分
        LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECO  ON YE.ECONOMY_TYPE=ECO.ECO_SEC_CAT_ID_3 AND ECO.IS_VALID= '1' AND '#V_DATA_DATE#' BETWEEN ECO.START_DATE AND ECO.END_DATE
        -- 担保方式
        LEFT JOIN DIMENSION.DIM_LOAN_GUARANTY_TYPE GUAR  ON YE.GUARANTY_TYPE=GUAR.LOAN_GUAR_TYPE_ID_2 AND GUAR.IS_VALID= '1' AND '#V_DATA_DATE#' BETWEEN GUAR.START_DATE AND GUAR.END_DATE
        WHERE YE.FIVE_CLASS IN ('FQ03','FQ04','FQ05')
          AND DATA_DATE='#V_DATA_DATE#' -- 本月末

        UNION ALL

        -- 上上月向下迁徙,余额取上月
        SELECT   YE.FIN_ORG_NO                      AS FIN_ORG_NO           -- 金融机构编码
                ,YE.CLIENT_TYPE                     AS CUSTOMER_TYPE        --  客户类型 单位客户=0；个人客户=1
                ,PRO.LOAN_PRODUCT_ID                AS PRODUCT_CATEGORY     --  贷款产品类别  展示一级
                ,YE.CLIENT_INDUSTRY                 AS LPIC                 --  企业分行业(贷款主体行业类别)
                ,ACTU.INDUSTRY_CODE_1               AS LOANS_ACTUALLY       --  行业投向  展示一级
                ,YE.ENTERPRISE_SCALE                AS ENTERPRISE_SCALE     --  企业规模 大、中、小、微型企业分别对应代码：CS01、CS02、CS03、CS04 非企业类单位对应代码：CS05。
                ,YE.TERM_CODE                       AS LOAN_PERIOD          --  贷款期限 1：6个月（含）以内 2：6个月至1年（含） 3：1至3年（含） 4：3至5年（含）5：5至10年（含）6：10年以上 7：未定期限"
                ,ECO.ECO_SEC_CAT_ID_2               AS ENTERPRISE_INVESTOR  -- 企业出资人经济成分 展示二级
                ,GUAR.LOAN_GUAR_TYPE_ID_1           AS GUARANTY_STYLE       -- 担保方式   展示一级
                ,YE.FIVE_CLASS                      AS LOAN_QUALITY         -- 贷款质量  FQ01-正常类贷款 FQ02-关注类贷款 FQ03-次级类贷款 FQ04-可疑类贷款 FQ05-损失类贷款
                ,YE.LOAN_STATUS                     AS LOAN_STATUS          -- 贷款状态 FS01-正常 FS02-展期 FS03-逾期 FS09-缩期 ...
                ,YE.CCY                             AS CCY                  -- 币种
                ,0                                  AS LOAN_BAL             -- 当月贷款余额
                ,COALESCE(YE.ACTUAL_BAL,0)          AS LOAN_BAL_LM          -- 上月贷款余额
                ,0                                  AS LOAN_BAL_LY          -- 去年同期贷款余额
                ,0                                  AS WSUM_BAL             -- 当月贷款余额加权值
                ,COALESCE(YE.WSUM_BAL,0)            AS WSUM_BAL_LM          -- 上月余额加权金额
                ,0                                  AS WSUM_BAL_LY          -- 去年同期余额加权金额
        FROM EDW.AG_LOAB_INFO YE
        -- 关联上上月贷款余额,取状态为正常类贷款,FQ02-关注类贷款
        LEFT JOIN (SELECT DUEBILL_NO FROM  EDW.AG_LOAB_INFO WHERE FIVE_CLASS IN ('FQ01','FQ02') AND DATA_DATE='#V_LAST_TWO_MONTH_DATE#') A ON A.DUEBILL_NO=YE.DUEBILL_NO
        -- 贷款产品类别
        LEFT JOIN DIMENSION.DIM_LOAN_PRODUCT PRO  ON YE.BUSINESS_TYPE=PRO.LOAN_PRODUCT_ID AND PRO.IS_VALID= '1' AND '#V_PREV_MONTH_DATE#' BETWEEN PRO.START_DATE AND PRO.END_DATE
        -- 实际投向
        LEFT JOIN DIMENSION.DIM_INDUSTRY ACTU  ON YE.LOAN_INDUSTRY=ACTU.INDUSTRY_CODE_3 AND ACTU.IS_VALID= '1' AND '#V_PREV_MONTH_DATE#' BETWEEN ACTU.START_DATE AND ACTU.END_DATE
        -- 企业出资人经济成分
        LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECO  ON YE.ECONOMY_TYPE=ECO.ECO_SEC_CAT_ID_3 AND ECO.IS_VALID= '1' AND '#V_PREV_MONTH_DATE#' BETWEEN ECO.START_DATE AND ECO.END_DATE
        -- 担保方式
        LEFT JOIN DIMENSION.DIM_LOAN_GUARANTY_TYPE GUAR  ON YE.GUARANTY_TYPE=GUAR.LOAN_GUAR_TYPE_ID_2 AND GUAR.IS_VALID= '1' AND '#V_PREV_MONTH_DATE#' BETWEEN GUAR.START_DATE AND GUAR.END_DATE
        WHERE YE.FIVE_CLASS IN ('FQ03','FQ04','FQ05')
          AND DATA_DATE='#V_PREV_MONTH_DATE#'  -- 上月末

        UNION ALL

        -- 去年同期上月向下迁徙,余额取去年同期值
        SELECT   YE.FIN_ORG_NO                      AS FIN_ORG_NO           -- 金融机构编码
                ,YE.CLIENT_TYPE                     AS CUSTOMER_TYPE        --  客户类型 单位客户=0；个人客户=1
                ,PRO.LOAN_PRODUCT_ID                AS PRODUCT_CATEGORY     --  贷款产品类别  展示一级
                ,YE.CLIENT_INDUSTRY                 AS LPIC                 --  企业分行业(贷款主体行业类别)
                ,ACTU.INDUSTRY_CODE_1               AS LOANS_ACTUALLY       --  行业投向  展示一级
                ,YE.ENTERPRISE_SCALE                AS ENTERPRISE_SCALE     --  企业规模 大、中、小、微型企业分别对应代码：CS01、CS02、CS03、CS04 非企业类单位对应代码：CS05。
                ,YE.TERM_CODE                       AS LOAN_PERIOD          --  贷款期限 1：6个月（含）以内 2：6个月至1年（含） 3：1至3年（含） 4：3至5年（含）5：5至10年（含）6：10年以上 7：未定期限"
                ,ECO.ECO_SEC_CAT_ID_2               AS ENTERPRISE_INVESTOR  -- 企业出资人经济成分 展示二级
                ,GUAR.LOAN_GUAR_TYPE_ID_1           AS GUARANTY_STYLE       -- 担保方式   展示一级
                ,YE.FIVE_CLASS                      AS LOAN_QUALITY         -- 贷款质量  FQ01-正常类贷款 FQ02-关注类贷款 FQ03-次级类贷款 FQ04-可疑类贷款 FQ05-损失类贷款
                ,YE.LOAN_STATUS                     AS LOAN_STATUS          -- 贷款状态 FS01-正常 FS02-展期 FS03-逾期 FS09-缩期 ...
                ,YE.CCY                             AS CCY                  -- 币种
                ,0                                  AS LOAN_BAL             -- 当月贷款余额
                ,0                                  AS LOAN_BAL_LM          -- 上月贷款余额
                ,COALESCE(YE.ACTUAL_BAL,0)          AS LOAN_BAL_LY          -- 去年同期贷款余额
                ,0                                  AS WSUM_BAL             -- 当月贷款余额加权值
                ,0                                  AS WSUM_BAL_LM          -- 上月余额加权金额
                ,COALESCE(YE.WSUM_BAL,0)            AS WSUM_BAL_LY          -- 去年同期余额加权金额
        FROM EDW.AG_LOAB_INFO YE
        -- 关联去年同期上个月贷款余额,取状态为正常类贷款,FQ02-关注类贷款
        LEFT JOIN (SELECT DUEBILL_NO FROM  EDW.AG_LOAB_INFO WHERE FIVE_CLASS IN ('FQ01','FQ02') AND DATA_DATE='#V_LAST_THIRTEEN_MONTH_DATE#') A ON A.DUEBILL_NO=YE.DUEBILL_NO
        -- 贷款产品类别
        LEFT JOIN DIMENSION.DIM_LOAN_PRODUCT PRO  ON YE.BUSINESS_TYPE=PRO.LOAN_PRODUCT_ID AND PRO.IS_VALID= '1' AND '#V_PREV_YEAR_DATE#' BETWEEN PRO.START_DATE AND PRO.END_DATE
        -- 实际投向
        LEFT JOIN DIMENSION.DIM_INDUSTRY ACTU  ON YE.LOAN_INDUSTRY=ACTU.INDUSTRY_CODE_3 AND ACTU.IS_VALID= '1' AND '#V_PREV_YEAR_DATE#' BETWEEN ACTU.START_DATE AND ACTU.END_DATE
        -- 企业出资人经济成分
        LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECO  ON YE.ECONOMY_TYPE=ECO.ECO_SEC_CAT_ID_3 AND ECO.IS_VALID= '1' AND '#V_PREV_YEAR_DATE#' BETWEEN ECO.START_DATE AND ECO.END_DATE
        -- 担保方式
        LEFT JOIN DIMENSION.DIM_LOAN_GUARANTY_TYPE GUAR  ON YE.GUARANTY_TYPE=GUAR.LOAN_GUAR_TYPE_ID_2 AND GUAR.IS_VALID= '1' AND '#V_PREV_YEAR_DATE#' BETWEEN GUAR.START_DATE AND GUAR.END_DATE
        WHERE YE.FIVE_CLASS IN ('FQ03','FQ04','FQ05')
          AND DATA_DATE='#V_PREV_YEAR_DATE#'  -- 去年同期
    ) T
    -- 与机构表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_BIZ_LVL WHERE IS_ACTIVE= '1')ORG ON T.FIN_ORG_NO=ORG.ORG_CODE_4 AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY  ORG.ORG_CODE_1
             ,ORG.ORG_DSCR_1
             ,ORG.ORG_CODE_2
             ,ORG.ORG_DSCR_2
             ,ORG.ORG_CODE_3
             ,ORG.ORG_DSCR_3
             ,AREA.AREA_CODE_1
             ,AREA.AREA_DSCR_1
             ,AREA.AREA_CODE_3
             ,AREA.AREA_DSCR_3
             ,AREA.AREA_CODE_4
             ,AREA.AREA_DSCR_4
             ,T.CUSTOMER_TYPE
             ,T.PRODUCT_CATEGORY
             ,T.LPIC
             ,T.LOANS_ACTUALLY
             ,T.ENTERPRISE_SCALE
             ,T.LOAN_PERIOD
             ,T.ENTERPRISE_INVESTOR
             ,T.GUARANTY_STYLE
             ,T.LOAN_QUALITY
             ,T.LOAN_STATUS
             ,T.CCY                 ;